Creating a Drop-down List with Data Validation in Excel

Follow these steps to create a drop-down list in Excel that pulls names from another worksheet.

Step 1: Create a List of Names

1. Open your Excel workbook and go to a separate worksheet (e.g., MasterList).
2. Enter the list of names in a single column (e.g., A1:A100).

Step 2: Name the List Range

1. Select the range containing the names.
2. Go to Formulas > Name Manager > New.
3. Enter a name for the range, e.g., NameList, and click OK.

Step 3: Apply Data Validation

1. Go to the worksheet where you want to enter names.
2. Select the column or cells where the drop-down list should appear.
3. Go to Data > Data Validation.
4. In the Allow dropdown, choose List.
5. In the Source field, enter: =NameList.
6. Click OK. Your drop-down list is now ready!

Step 4: Test the Drop-down List

Click on a cell where you applied data validation. You should see a drop-down arrow allowing you to select a name from the list.

Bonus: Allow Users to Enter New Names

To allow users to type in names not in the list, go to Data Validation > Error Alert and uncheck Show error alert after invalid data is entered.